using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using SOCONINC.Base;
using SOCONINC.EII.Repository;

namespace SOCONINC.EII
{
	public class DataAccessOld : DataAccessBase, IServiceProvider
	{
		public DataAccessOld(IServiceProvider serviceProvider, SOCONINC.ProcessData.DataAccess processData)
			: base(processData.ConnectString)
		{
			_processData = processData;
			_serviceProvider = serviceProvider;
		}

		private SOCONINC.ProcessData.DataAccess _processData;
		public SOCONINC.ProcessData.DataAccess ProcessData
		{
			get { return _processData; }
			set { _processData = value; }
		}

		private IServiceProvider _serviceProvider;

		#region Old Repository Methods
		#region methods for domains
		public int domains_update(int domainId, string domainNm, string domainDesc,
								  Domain.DomainType domainType, string domainNtext,
								  XmlDocument domainSchema, int parentDomainId)
		{
			int idOrg = _processData.UserData.OrganizationID;
			int iRet = -1;
			SqlParameter[] sqlParams =
            {
                new SqlParameter("@domain_id", domainId),
                new SqlParameter("@domain_nm", domainNm),
                new SqlParameter("@domain_desc", domainDesc),
                new SqlParameter("@domain_type", (int)domainType),
                new SqlParameter("@domain_ntext", domainNtext),
                (domainSchema != null ? new SqlParameter("@domain_schema", domainSchema.InnerXml) :
										new SqlParameter("@domain_schema", DBNull.Value)),
                (parentDomainId > 0 ? new SqlParameter("@parent_domain_id", parentDomainId) :
									  new SqlParameter("@parent_domain_id", DBNull.Value)),
				new SqlParameter("@org_id", idOrg)
            };

			if ( (int)execScalar("SELECT count(*) FROM domains WHERE domain_id = @domain_id AND org_id = @org_id",
								new SqlParameter("@domain_id", domainId),
								new SqlParameter("@org_id", idOrg)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO domains (domain_nm, domain_desc, domain_type, domain_ntext, " +
						"domain_schema, parent_domain_id, org_id) " +
					"VALUES (@domain_nm, @domain_desc, @domain_type, @domain_ntext, @domain_schema, " +
						"@parent_domain_id, @org_id); " +
					"SELECT domain_id FROM domains WHERE domain_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE domains SET domain_nm = @domain_nm, domain_desc = @domain_desc, " +
						"domain_type = @domain_type, domain_ntext = @domain_ntext, " +
						"domain_schema = @domain_schema, parent_domain_id = @parent_domain_id, " +
						"org_id = @org_id " +
					"WHERE domain_id = @domain_id; " +
					"SELECT @domain_id",
					sqlParams);
			}

			return iRet;
		}
		public int domains_delete(int domainId)
		{
			DataTable dt;
			execNonQuery("DELETE FROM domain_business_rules WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));

			dt = fillDataTable("SELECT bobj_id FROM domain_business_objects WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));
			foreach ( DataRow row in dt.Rows ) {
				domain_business_objects_delete((int)row["bobj_id"]);
			}

			dt = fillDataTable("SELECT service_id FROM domain_services WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));
			foreach ( DataRow row in dt.Rows ) {
				services_delete((int)row["service_id"]);
			}

			dt = fillDataTable("SELECT domvocab_id FROM domain_vocabulary WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));
			foreach ( DataRow row in dt.Rows ) {
				domain_vocabulary_delete((int)row["domvocab_id"]);
			}

			return execNonQuery("DELETE FROM domains WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable domains_select(int domainId)
		{
			return fillDataTable("SELECT * FROM domains WHERE domain_id = @domain_id",
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable domains_select()
		{
			return fillDataTable("SELECT * FROM domains WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}

		public Domain LoadDomain(int domainId)
		{
			Domain domain = null;

			DataTable dtDom = domains_select(domainId);
			if ( dtDom == null || dtDom.Rows.Count == 0 )
				return null;

			domain = new Domain();
			//domain.Load(dtDom.Rows[0]);

			//DataTable dtVoc = domain_vocabulary_select_by_domain(domainId);
			//if ( dtVoc != null && dtVoc.Rows.Count > 0 ) {
			//    domain.LoadVocabulary(dtVoc.Select());
			//}
			//DataTable dtSvc = services_select_by_domain(domainId);
			//if ( dtSvc != null && dtSvc.Rows.Count > 0 ) {
			//    domain.LoadServices(dtSvc.Select());
			//    foreach ( Service svc in domain.Services ) {
			//        DataTable dtSM = svcmethods_select_by_service(svc.ID);
			//        if ( dtSM != null && dtSM.Rows.Count > 0 )
			//            svc.LoadMethods(dtSM.Select());
			//    }
			//}

			return domain;
		}
		public DataRow SaveDomain(Domain domain)
		{
			DataRow row = null;
			//int id = domains_update(domain.ID, domain.Name, domain.Description, domain.Type,
			//                        domain.NText, domain.Schema, 0);
			//DataTable dt = domains_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion

		#region methods for domain_vocabulary
		public int domain_vocabulary_update(int domvocabId, string domvocabNm, string domvocabDesc,
											Noun.NounType domvocabType,
											string domvocabDatatype, int parentDomvocabId, int domainId)
		{
			int idOrg = _processData.UserData.OrganizationID;
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@domvocab_id", domvocabId),
                new SqlParameter("@domvocab_nm", domvocabNm),
                new SqlParameter("@domvocab_desc", domvocabDesc),
                new SqlParameter("@domvocab_type", (int)domvocabType),
                (domvocabDatatype != null && domvocabDatatype != "" ?
					new SqlParameter("@domvocab_datatype", domvocabDatatype) :
					new SqlParameter("@domvocab_datatype", DBNull.Value)),
                (parentDomvocabId > 0 ? new SqlParameter("@parent_domvocab_id", parentDomvocabId) :
										new SqlParameter("@parent_domvocab_id", DBNull.Value)),
                (domainId > 0 ? new SqlParameter("@domain_id", domainId) :
								new SqlParameter("@domain_id", DBNull.Value)),
				new SqlParameter("@org_id", idOrg)
            };

			if ( (int)execScalar("SELECT count(*) FROM domain_vocabulary WHERE domvocab_id = @domvocab_id",
								new SqlParameter("@domvocab_id", domvocabId)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO domain_vocabulary (domvocab_nm, domvocab_desc, domvocab_type, " +
						"domvocab_datatype, parent_domvocab_id, domain_id, org_id) " +
					"VALUES (@domvocab_nm, @domvocab_desc, @domvocab_type, @domvocab_datatype, " +
						"@parent_domvocab_id, @domain_id, @org_id); " +
					"SELECT domvocab_id FROM domain_vocabulary WHERE domvocab_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE domain_vocabulary SET domvocab_nm = @domvocab_nm, " +
						"domvocab_desc = @domvocab_desc, domvocab_type = @domvocab_type, " +
						"domvocab_datatype = @domvocab_datatype, " +
						"parent_domvocab_id = @parent_domvocab_id, domain_id = @domain_id, " +
						"org_id = @org_id " +
					"WHERE domvocab_id = @domvocab_id; " +
					"SELECT @domvocab_id",
					sqlParams);
			}
			return iRet;
		}
		public int domain_vocabulary_delete(int domvocabId)
		{
			DataTable dt;
			execNonQuery("DELETE FROM domain_business_object_vocabulary WHERE domvocab_id = @domvocab_id",
				new SqlParameter("@domvocab_id", domvocabId));

			dt = fillDataTable("SELECT domvocab_id FROM domain_vocabulary WHERE parent_domvocab_id = @parent_domvocab_id",
				new SqlParameter("@parent_domvocab_id", domvocabId));
			foreach ( DataRow row in dt.Rows ) {
				domain_vocabulary_delete((int)row["domvocab_id"]);
			}

			return execNonQuery("DELETE FROM domain_vocabulary WHERE domvocab_id = @domvocab_id",
				new SqlParameter("@domvocab_id", domvocabId));
		}
		public DataTable domain_vocabulary_select(int domvocabId)
		{
			return fillDataTable("SELECT * FROM domain_vocabulary WHERE domvocab_id = @domvocab_id",
				new SqlParameter("@domvocab_id", domvocabId));
		}
		public DataTable domain_vocabulary_select()
		{
			return fillDataTable("SELECT * FROM domain_vocabulary WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable root_domain_vocabulary_select()
		{
			return fillDataTable(
				"SELECT * FROM domain_vocabulary WHERE domain_id IS NULL AND org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable domain_vocabulary_select_by_domain(int domain_id)
		{
			return fillDataTable("SELECT * FROM domain_vocabulary WHERE domain_id = @domain_id AND org_id = @org_id",
				new SqlParameter("@domain_id", domain_id),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable domain_vocabulary_select_by_bobject(int bobjectId)
		{
			return new DataTable();
			//return fillDataTable(
			//    "SELECT dv.* FROM domain_vocabulary dv " +
			//    "INNER JOIN domain_business_object_vocabulary bov ON bov.domvocab_id = dv.domvocab_id " +
			//    "WHERE bov.bobj_id = @bobj_id",
			//    new SqlParameter("@bobj_id", bobjectId));
		}

		public Noun LoadDomainVariable(int domvocabId)
		{
			Noun var = null;
			DataTable dtVar = domain_vocabulary_select(domvocabId);
			if ( dtVar != null && dtVar.Rows.Count > 0 ) {
				var = new Noun();
				//var.Load(dtVar.Rows[0]);
			}
			return var;
		}
		public DataRow SaveDomainVariable(Noun variable)
		{
			DataRow row = null;
			//int id = domain_vocabulary_update(variable.ID, variable.Name, variable.Description,
			//                         variable.Type, variable.DataType, variable.ParentID,
			//                         variable.DomainID);
			//DataTable dt = domain_vocabulary_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion

		#region methods for datatypes
		public int datatypes_update(string dataType, string dataTypeNm, string dataTypeDesc)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@datatype", dataType),
                new SqlParameter("@datatype_nm", dataTypeNm),
                new SqlParameter("@datatype_desc", dataTypeDesc)
            };

			if ( (int)execScalar("SELECT count(*) FROM datatypes WHERE datatype = @datatype",
								new SqlParameter("@datatype", dataType)) == 0 ) {
				iRet = execNonQuery("INSERT INTO datatypes (datatype_nm, datatype_desc)"
									 + " VALUES (@datatype_nm, @datatype_desc)", sqlParams);
			} else {
				iRet = execNonQuery("UPDATE datatypes SET datatype_nm = @datatype_nm, datatype_desc = @datatype_desc WHERE datatype = @datatype", sqlParams);
			}

			return iRet;
		}
		public int datatypes_delete(string dataType)
		{
			return execNonQuery("DELETE FROM datatypes WHERE datatype = @datatype",
				new SqlParameter("@datatype", dataType));
		}
		public DataTable datatypes_select(string dataType)
		{
			return fillDataTable("SELECT * FROM datatypes WHERE datatype = @datatype",
			   new SqlParameter("@datatype", dataType));
		}
		public DataTable datatypes_select()
		{
			return fillDataTable("SELECT * FROM datatypes");
		}
		#endregion

		#region methods for systems
		public int systems_update(int systemId, string systemNm, string systemDesc, int systemType, string systemNtext, string systemVersion, string systemAdministrator, string systemUri, string systemWsdl, string inputSchema, string outputSchema, int domainId)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@system_id", systemId),
                new SqlParameter("@system_nm", systemNm),
                new SqlParameter("@system_desc", systemDesc),
                new SqlParameter("@system_type", systemType),
                new SqlParameter("@system_ntext", systemNtext),
                new SqlParameter("@system_version", systemVersion),
                new SqlParameter("@system_administrator", systemAdministrator),
                new SqlParameter("@system_uri", systemUri),
                new SqlParameter("@system_wsdl", systemWsdl),
                new SqlParameter("@input_schema", inputSchema),
                new SqlParameter("@output_schema", outputSchema),
                new SqlParameter("@domain_id", domainId)
            };

			if ( (int)execScalar("SELECT count(*) FROM systems WHERE system_id = @system_id",
								new SqlParameter("@system_id", systemId)) == 0 ) {
				iRet = execNonQuery("INSERT INTO systems (system_nm, system_desc, system_type, system_ntext, system_version, system_administrator, system_uri, system_wsdl, input_schema, output_schema, domain_id)"
									 + " VALUES (@system_nm, @system_desc, @system_type, @system_ntext, @system_version, @system_administrator, @system_uri, @system_wsdl, @input_schema, @output_schema, @domain_id)", sqlParams);
			} else {
				iRet = execNonQuery("UPDATE systems SET system_nm = @system_nm, system_desc = @system_desc, system_type = @system_type, system_ntext = @system_ntext, system_version = @system_version, system_administrator = @system_administrator, system_uri = @system_uri, system_wsdl = @system_wsdl, input_schema = @input_schema, output_schema = @output_schema, domain_id = @domain_id WHERE system_id = @system_id", sqlParams);
			}

			return iRet;
		}
		public int systems_delete(int systemId)
		{
			return execNonQuery("DELETE FROM systems WHERE system_id = @system_id",
			   new SqlParameter("@system_id", systemId));
		}
		public DataTable systems_select(int systemId)
		{
			return fillDataTable("SELECT * FROM systems WHERE system_id = @system_id",
				new SqlParameter("@system_id", systemId));
		}
		public DataTable systems_select()
		{
			return fillDataTable("SELECT * FROM systems");
		}
		#endregion

		#region methods for domain_integrations
		public int domain_integrations_update(int integrationId, int domainId)
		{
			return execNonQuery(
				"INSERT INTO domain_integrations (integration_id, domain_id) " +
				"VALUES (@integration_id, @domain_id)",
				new SqlParameter("@integration_id", integrationId),
				new SqlParameter("@domain_id", domainId));
		}
		public int domain_integrations_delete(int integrationId, int domainId)
		{
			return execNonQuery(
				"DELETE FROM domain_integrations " +
				"WHERE integration_id = @integration_id AND domain_id = @domain_id",
				new SqlParameter("@integration_id", integrationId),
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable domain_integrations_select(int integrationId, int domainId)
		{
			return fillDataTable(
				"SELECT * FROM domain_integrations " +
				"WHERE integration_id = @integration_id AND domain_id = @domain_id",
				new SqlParameter("@integration_id", integrationId),
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable domain_integrations_select()
		{
			return fillDataTable("SELECT * FROM domain_integrations");
		}
		#endregion

		#region methods for system_integrations
		public int system_integrations_update(int integrationId, int systemId, int inputWebmethodId, int asyncOutputWebmethodId, string inputMapping, string outputMapping)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@integration_id", integrationId),
                new SqlParameter("@system_id", systemId),
                new SqlParameter("@input_webmethod_id", inputWebmethodId),
                new SqlParameter("@async_output_webmethod_id", asyncOutputWebmethodId),
                new SqlParameter("@input_mapping", inputMapping),
                new SqlParameter("@output_mapping", outputMapping)
            };

			if ( (int)execScalar("SELECT count(*) FROM system_integrations " +
				"WHERE integrations_id = @integrations_id AND system_id = @system_id AND " +
				" input_webmethod_id = @input_webmethod_id AND " +
				" async_output_webmethod_id = @async_outputwebmethod_id",
				new SqlParameter("@integrations_id", integrationId),
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@input_webmethod_id", inputWebmethodId),
				new SqlParameter("@async_outputwebmethod_id", asyncOutputWebmethodId)) == 0 ) {
				iRet = execNonQuery("INSERT INTO system_integrations (integration_id, system_id, input_webmethod_id, async_output_webmethod_id, input_mapping, output_mapping)"
									 + " VALUES (@integration_id, @system_id, @input_webmethod_id, @async_output_webmethod_id, @input_mapping, @output_mapping)", sqlParams);
			} else {
				iRet = execNonQuery("UPDATE system_integrations SET input_mapping = @input_mapping, output_mapping = @output_mapping WHERE integrations_id = @integrations_id AND system_id = @system_id AND input_webmethod_id = @input_webmethod_id AND async_output_webmethod_id = @async_outputwebmethod_id", sqlParams);
			}

			return iRet;
		}
		public int system_integrations_delete(int integrationId, int systemId, int inputWebmethodId, int asyncOutputWebmethodId)
		{
			return execNonQuery(
				"DELETE FROM system_integrations " +
				"WHERE integrations_id = @integrations_id AND system_id = @system_id " +
					"AND input_webmethod_id = @input_webmethod_id " +
					"AND async_output_webmethod_id = @async_outputwebmethod_id",
				new SqlParameter("@integration_id", integrationId),
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@input_webmethod_id", inputWebmethodId),
				new SqlParameter("@async_outputwebmethod_id", asyncOutputWebmethodId));
		}
		public DataTable system_integrations_select(int integrationId, int systemId, int inputWebmethodId, int asyncOutputWebmethodId)
		{
			return fillDataTable(
				"SELECT * FROM system_integrations " +
				"WHERE integrations_id = @integrations_id AND system_id = @system_id " +
					"AND input_webmethod_id = @input_webmethod_id " +
					"AND async_output_webmethod_id = @async_outputwebmethod_id",
				new SqlParameter("@integration_id", integrationId),
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@input_webmethod_id", inputWebmethodId),
				new SqlParameter("@async_outputwebmethod_id", asyncOutputWebmethodId));
		}
		public DataTable system_integrations_select()
		{
			return fillDataTable("SELECT * FROM system_integrations");
		}
		#endregion

		#region methods for integrations
		public int integrations_update(int integrationId, string integrationNm, string integrationDesc, string integrationNtext, string integrationSchema)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@integration_id", integrationId),
                new SqlParameter("@integration_nm", integrationNm),
                new SqlParameter("@integration_desc", integrationDesc),
                new SqlParameter("@integration_ntext", integrationNtext),
                new SqlParameter("@integration_schema", integrationSchema)
            };

			if ( (int)execScalar(
				"SELECT count(*) FROM integrations WHERE integration_id = @integration_id",
				new SqlParameter("@integration_id", integrationId)) == 0 ) {
				iRet = execNonQuery(
					"INSERT INTO integrations " +
						"(integration_nm, integration_desc, integration_ntext, integration_schema)" +
					"VALUES (@integration_nm, @integration_desc, @integration_ntext, @integration_schema)",
					sqlParams);
			} else {
				iRet = execNonQuery(
					"UPDATE integrations SET integration_nm = @integration_nm, " +
						"integration_desc = @integration_desc, integration_ntext = @integration_ntext, " +
						"integration_schema = @integration_schema WHERE integration_id = @integration_id",
					sqlParams);
			}

			return iRet;
		}
		public int integrations_delete(int integrationId)
		{
			return execNonQuery("DELETE FROM integrations WHERE integration_id = @integration_id",
				new SqlParameter("@integration_id", integrationId));
		}
		public DataTable integrations_select(int integrationId)
		{
			return fillDataTable("SELECT * FROM integrations WHERE integration_id = @integration_id",
				new SqlParameter("@integration_id", integrationId));
		}
		public DataTable integrations_select()
		{
			return fillDataTable("SELECT * FROM integrations");
		}
		#endregion

		#region methods for services
		public int services_update(int serviceId, string serviceNm, string serviceDesc,
								   Service.ServiceType serviceType, string serviceNtext,
								   int systemId)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@service_id", serviceId),
                new SqlParameter("@service_nm", serviceNm),
                new SqlParameter("@service_desc", serviceDesc),
                new SqlParameter("@service_type", (int)serviceType),
                new SqlParameter("@service_ntext", serviceNtext),
                (systemId > 0 ? new SqlParameter("@system_id", systemId) :
								new SqlParameter("@system_id", DBNull.Value)),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID)
            };

			if ( (int)execScalar("SELECT count(*) FROM services WHERE service_id = @service_id",
								new SqlParameter("@service_id", serviceId)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO services (service_nm, service_desc, service_type, service_ntext, " +
						"system_id, org_id) " +
					"VALUES (@service_nm, @service_desc, @service_type, @service_ntext, " +
						"@system_id, @org_id); " +
					"SELECT service_id FROM services WHERE service_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE services SET service_nm = @service_nm, service_desc = @service_desc, " +
						"service_type = @service_type, service_ntext = @service_ntext, " +
						"system_id = @system_id, " +
						"org_id = @org_id " +
					"WHERE service_id = @service_id; " +
					"SELECT @service_id",
					sqlParams);
			}

			return iRet;
		}
		public int services_delete(int serviceId)
		{
			DataTable dt = fillDataTable(
				"SELECT operation_id FROM operations WHERE service_id = @service_id",
				new SqlParameter("@service_id", serviceId));
			foreach ( DataRow row in dt.Rows ) {
				svcmethods_delete((int)row["operation_id"]);
			}
			execNonQuery("DELETE FROM domain_services WHERE service_id = @service_id",
				new SqlParameter("@service_id", serviceId));
			return execNonQuery("DELETE FROM services WHERE service_id = @service_id",
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable services_select(int serviceId)
		{
			return fillDataTable("SELECT * FROM services WHERE service_id = @service_id",
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable services_select()
		{
			return fillDataTable("SELECT * FROM services WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable root_services_select()
		{
			return fillDataTable(
				"SELECT * FROM services WHERE service_id NOT IN " +
					"(SELECT service_id FROM domain_services) AND org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable services_select_by_domain(int domainId)
		{
			return fillDataTable(
				"SELECT * FROM services WHERE service_id IN " +
					"(SELECT service_id FROM domain_services WHERE domain_id = @domain_id)",
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable services_select_by_parent_service(int parentServiceId)
		{
			return fillDataTable(
				"SELECT s.*, d.domain_id, d.domain_nm FROM services s " +
				"INNER JOIN domain_services ds ON ds.service_id = s.service_id " +
				"INNER JOIN domains d ON d.domain_id = ds.domain_id " +
				"INNER JOIN service_hierarchy sh ON sh.child_service_id = s.service_id " +
				"WHERE sh.parent_service_id = @parent_service_id",
				new SqlParameter("@parent_service_id", parentServiceId));
		}

		public Service LoadService(int serviceId)
		{
			Service svc = null;
			DataTable dtSvc = services_select(serviceId);
			if ( dtSvc != null && dtSvc.Rows.Count > 0 ) {
				svc = new Service();
				//svc.Load(dtSvc.Rows[0]);

				//DataTable dtSM = svcmethods_select_by_service(serviceId);
				//if ( dtSM != null && dtSM.Rows.Count > 0 )
				//    svc.LoadMethods(dtSM.Select());
			}
			return svc;
		}
		public DataRow SaveService(Service service)
		{
			DataRow row = null;
			//int id = services_update(service.ID, service.Name, service.Description, service.Type,
			//                service.NText, -1);
			//DataTable dt = services_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion

		#region service_hierarchy

		public int service_hierarchy_update(int parentServiceId, int childServiceId)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM service_hierarchy " +
					"WHERE parent_service_id = @parent_service_id AND child_service_id = @child_service_id ) " +
				"BEGIN " +
					"INSERT INTO service_hierarchy (parent_service_id, child_service_id) " +
					"VALUES (@parent_service_id, @child_service_id); " +
				"END",
				new SqlParameter("@parent_service_id", parentServiceId),
				new SqlParameter("@child_service_id", childServiceId));
		}
		public int service_hierarchy_delete(int parentServiceId, int childServiceId)
		{
			return execNonQuery(
				"DELETE FROM service_hierarchy " +
				"WHERE parent_service_id = @parent_service_id AND child_service_id = @child_service_id",
			   new SqlParameter("@parent_service_id", parentServiceId),
			   new SqlParameter("@child_service_id", childServiceId));
		}

		#endregion service_hierarchy

		#region methods for svcmethods
		public int svcmethods_update(int svcmethodId, string svcmethodNm, string svcmethodDesc,
									 XmlDocument svcmethodInputSchema, bool bIsInputBObjXsd,
									 XmlDocument svcmethodOutputSchema, bool bIsOutputBObjXsd,
									 string svcmethodInputFormStyle, string svcmethodOutputFormStyle,
									 int serviceId)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@svcmethod_id", svcmethodId),
                new SqlParameter("@svcmethod_nm", svcmethodNm),
                new SqlParameter("@svcmethod_desc", svcmethodDesc),
                (svcmethodInputSchema != null ?
					new SqlParameter("@svcmethod_input_schema", svcmethodInputSchema.DocumentElement.OuterXml) :
					new SqlParameter("@svcmethod_input_schema", DBNull.Value)),
                new SqlParameter("@svcmethod_input_isbobjxsd", bIsInputBObjXsd),
				(svcmethodOutputSchema != null ?
					new SqlParameter("@svcmethod_output_schema", svcmethodOutputSchema.DocumentElement.OuterXml) :
					new SqlParameter("@svcmethod_output_schema", DBNull.Value)),
                new SqlParameter("@svcmethod_output_isbobjxsd", bIsOutputBObjXsd),
                (svcmethodInputFormStyle != null ?
					new SqlParameter("@svcmethod_input_formstyle", svcmethodInputFormStyle) :
					new SqlParameter("@svcmethod_input_formstyle", DBNull.Value)),
				(svcmethodOutputFormStyle != null ?
					new SqlParameter("@svcmethod_output_formstyle", svcmethodOutputFormStyle) :
					new SqlParameter("@svcmethod_output_formstyle", DBNull.Value)),
                new SqlParameter("@service_id", serviceId)
            };

			if ( (int)execScalar("SELECT count(*) FROM svcmethods WHERE svcmethod_id = @svcmethod_id",
								 new SqlParameter("@svcmethod_id", svcmethodId)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO svcmethods (svcmethod_nm, svcmethod_desc, svcmethod_input_schema, " +
						"svcmethod_input_isbobjxsd, svcmethod_output_schema, svcmethod_output_isbobjxsd, " +
						"svcmethod_input_formstyle, svcmethod_output_formstyle, " +
						"service_id) " +
					"VALUES (@svcmethod_nm, @svcmethod_desc, @svcmethod_input_schema, " +
						"@svcmethod_input_isbobjxsd, @svcmethod_output_schema, @svcmethod_output_isbobjxsd, " +
						"@svcmethod_input_formstyle, @svcmethod_output_formstyle, " +
						"@service_id); " +
					"SELECT svcmethod_id FROM svcmethods WHERE svcmethod_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE svcmethods SET svcmethod_nm = @svcmethod_nm, svcmethod_desc = @svcmethod_desc, " +
						"svcmethod_input_schema = @svcmethod_input_schema, " +
						"svcmethod_input_isbobjxsd = @svcmethod_input_isbobjxsd, " +
						"svcmethod_output_schema = @svcmethod_output_schema, " +
						"svcmethod_output_isbobjxsd = @svcmethod_output_isbobjxsd, " +
						"svcmethod_input_formstyle = @svcmethod_input_formstyle, " +
						"svcmethod_output_formstyle = @svcmethod_output_formstyle, " +
						"service_id = @service_id " +
					"WHERE svcmethod_id = @svcmethod_id; " +
					"SELECT @svcmethod_id",
					sqlParams);
			}

			return iRet;
		}
		public int svcmethods_delete(int svcmethodId)
		{
			execNonQuery(
				"DELETE FROM svcmethod_hierarchy " +
				"WHERE parent_svcmethod_id = @svcmethod_id OR child_svcmethod_id = @svcmethod_id",
				new SqlParameter("@svcmethod_id", svcmethodId));
			execNonQuery(
				"DELETE FROM svcmethod_bpel_process WHERE svcmethod_id = @svcmethod_id",
				new SqlParameter("@svcmethod_id", svcmethodId));
			execNonQuery(
				"DELETE FROM svcmethod_kpi WHERE svcmethod_id = @svcmethod_id",
				new SqlParameter("@svcmethod_id", svcmethodId));
			execNonQuery(
				"DELETE FROM svcmethod_log_ep WHERE svcmethod_id = @svcmethod_id",
				new SqlParameter("@svcmethod_id", svcmethodId));
			return execNonQuery(
				"DELETE FROM svcmethods WHERE svcmethod_id = @svcmethod_id",
				new SqlParameter("@svcmethod_id", svcmethodId));
		}
		public DataTable svcmethods_select(int svcmethodId)
		{
			return fillDataTable(
				"SELECT sm.*, s.service_nm, d.domain_id, d.domain_nm FROM svcmethods sm " +
				"INNER JOIN services s ON s.service_id = sm.service_id " +
				"INNER JOIN domain_services ds ON ds.service_id = s.service_id " +
				"INNER JOIN domains d ON d.domain_id = ds.domain_id " +
				"WHERE sm.svcmethod_id = @svcmethod_id",
			   new SqlParameter("@svcmethod_id", svcmethodId));
		}
		public DataTable svcmethods_select()
		{
			return fillDataTable(
				"SELECT sm.*, s.service_nm, d.domain_id, d.domain_nm FROM svcmethods sm " +
				"INNER JOIN services s ON s.service_id = sm.service_id " +
				"INNER JOIN domain_services ds ON ds.service_id = s.service_id " +
				"INNER JOIN domains d ON d.domain_id = ds.domain_id");
		}
		public DataTable svcmethods_select_by_service(int serviceId)
		{
			return fillDataTable(
				"SELECT sm.*, s.service_nm, d.domain_id, d.domain_nm FROM svcmethods sm " +
				"INNER JOIN services s ON s.service_id = sm.service_id " +
				"INNER JOIN domain_services ds ON ds.service_id = s.service_id " +
				"INNER JOIN domains d ON d.domain_id = ds.domain_id " +
				"WHERE sm.service_id = @service_id",
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable svcmethods_select_by_parent_svcmethod(int parentSvcMethodId)
		{
			return fillDataTable(
				"SELECT sm.*, s.service_nm, d.domain_id, d.domain_nm FROM svcmethods sm " +
				"INNER JOIN svcmethod_hierarchy smh ON smh.child_svcmethod_id = sm.svcmethod_id " +
				"INNER JOIN services s ON s.service_id = sm.service_id " +
				"INNER JOIN domain_services ds ON ds.service_id = s.service_id " +
				"INNER JOIN domains d ON d.domain_id = ds.domain_id " +
				"WHERE smh.parent_svcmethod_id = @parent_svcmethod_id",
				new SqlParameter("@parent_svcmethod_id", parentSvcMethodId));
		}

		public Operation LoadServiceMethod(int svcmethodId)
		{
			Operation mth = null;
			DataTable dtMth = svcmethods_select(svcmethodId);
			if ( dtMth != null && dtMth.Rows.Count > 0 ) {
				mth = new Operation();
				//mth.Load(dtMth.Rows[0]);
			}
			return mth;
		}
		public DataRow SaveServiceMethod(Operation svcmethod)
		{
			DataRow row = null;
			//int id = svcmethods_update(svcmethod.ID, svcmethod.Name, svcmethod.Description,
			//                  svcmethod.InputSchema, svcmethod.IsInputBusinessObjectSchema,
			//                  svcmethod.OutputSchema, svcmethod.IsOutputBusinessObjectSchema,
			//                  svcmethod.InputFormStyle, svcmethod.OutputFormStyle,
			//                  svcmethod.ServiceID);
			//DataTable dt = svcmethods_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion

		#region svcmethod_hierarchy

		public int svcmethod_hierarchy_update(int parentSvcMethodId, int childSvcMethodId)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM svcmethod_hierarchy " +
					"WHERE parent_svcmethod_id = @parent_svcmethod_id AND child_svcmethod_id = @child_svcmethod_id ) " +
				"BEGIN " +
					"INSERT INTO svcmethod_hierarchy (parent_svcmethod_id, child_svcmethod_id) " +
					"VALUES (@parent_svcmethod_id, @child_svcmethod_id) " +
				"END",
				new SqlParameter("@parent_svcmethod_id", parentSvcMethodId),
				new SqlParameter("@child_svcmethod_id", childSvcMethodId));
		}
		public int svcmethod_hierarchy_delete(int parentSvcMethodId, int childSvcMethodId)
		{
			return execNonQuery(
				"DELETE FROM svcmethod_hierarchy " +
				"WHERE parent_svcmethod_id = @parent_svcmethod_id AND child_svcmethod_id = @child_svcmethod_id",
			   new SqlParameter("@parent_svcmethod_id", parentSvcMethodId),
			   new SqlParameter("@child_svcmethod_id", childSvcMethodId));
		}

		#endregion svcmethod_hierarchy

		#region methods for webservices
		public int webservices_update(int websvcId, string websvcNm, string websvcDesc, int websvcType, string websvcUri, string websvcNtxt, string websvcWsdl, string websvcXmins, int serviceId)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@websvc_id", websvcId),
                new SqlParameter("@websvc_nm", websvcNm),
                new SqlParameter("@websvc_desc", websvcDesc),
                new SqlParameter("@websvc_type", websvcType),
                new SqlParameter("@websvc_uri", websvcUri),
                new SqlParameter("@websvc_ntext", websvcNtxt),
                new SqlParameter("@websvc_wsdl", websvcWsdl),
                new SqlParameter("@websvc_xmins", websvcXmins),
                new SqlParameter("@service_id", serviceId)
            };

			if ( (int)execScalar("SELECT count(*) FROM webservices WHERE websvc_id = @websvc_id",
								new SqlParameter("@websvc_id", websvcId)) == 0 ) {
				iRet = execNonQuery("INSERT INTO webservices (websvc_nm, websvc_desc, websvc_type, websvc_uri, websvc_ntext, websvc_wsdl, websvc_xmins, service_id)"
									 + " VALUES (@websvc_nm, @websvc_desc, @websvc_type, @websvc_uri, @websvc_ntext, @websvc_wsdl, @websvc_xmins, @service_id)", sqlParams);
			} else {
				iRet = execNonQuery("UPDATE webservices SET websvc_nm = @websvc_nm, websvc_desc = @websvc_desc, websvc_type = @websvc_type, websvc_uri = @websvc_uri, websvc_ntext = @websvc_ntext, websvc_wsdl = @websvc_wsdl, websvc_xmins = @websvc_wsdl, service_id = @websvc_wsdl WHERE websvc_id = @websvc_id", sqlParams);
			}

			return iRet;
		}
		public int webservices_delete(int websvcId)
		{
			return execNonQuery("DELETE FROM webservices WHERE websvc_id = @websvc_id",
				new SqlParameter("@websvc_id", websvcId));
		}
		public DataTable webservices_select(int websvcId)
		{
			return fillDataTable("SELECT * FROM webservices WHERE websvc_id = @websvc_id",
				new SqlParameter("@websvc_id", websvcId));
		}
		public DataTable webservices_select()
		{
			return fillDataTable("SELECT * FROM webservices");
		}
		#endregion

		#region methods for webmethods
		public int webmethods_update(int webmethodId, string webmethodNm, string webmethodDesc, string webmethodAction, string webmethodInputSchema, string webmethodOutputSchema, int websvcId, int serviceId)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@webmethod_id", webmethodId),
                new SqlParameter("@webmethod_nm", webmethodNm),
                new SqlParameter("@webmethod_desc", webmethodDesc),
                new SqlParameter("@webmethod_action", webmethodAction),
                new SqlParameter("@webmethod_input_schema", webmethodInputSchema),
                new SqlParameter("@webmethod_output_schema", webmethodOutputSchema),
                new SqlParameter("@websvc_id", websvcId),
                new SqlParameter("@service_id", serviceId)
            };

			if ( (int)execScalar("SELECT count(*) FROM webmethods WHERE webmethod_id = @webmethod_id",
								new SqlParameter("@webmethod_id", webmethodId)) == 0 ) {
				iRet = execNonQuery("INSERT INTO webmethods (webmethod_nm, webmethod_desc, webmethod_action, webmethod_input_schema, webmethod_output_schema, websvc_id, service_id)"
									 + " VALUES (@webmethod_nm, @webmethod_desc, @webmethod_action, @webmethod_input_schema, @webmethod_output_schema, @websvc_id, @service_id)", sqlParams);
			} else {
				iRet = execNonQuery("UPDATE webmethods SET webmethod_nm = @webmethod_nm, webmethod_desc = @webmethod_desc, webmethod_action = @webmethod_action, webmethod_input_schema = @webmethod_input_schema, webmethod_output_schema = @webmethod_output_schema, websvc_id = @websvc_id, service_id = @service_id WHERE webmethod_id = @webmethod_id", sqlParams);
			}

			return iRet;
		}
		public int webmethods_delete(int webmethodId)
		{
			return execNonQuery("DELETE FROM webmethods WHERE webmethod_id = @webmethod_id",
			   new SqlParameter("@webmethod_id", webmethodId));
		}
		public DataTable webmethods_select(int webmethodId)
		{
			return fillDataTable("SELECT * FROM webmethods WHERE webmethod_id = @webmethod_id",
			   new SqlParameter("@webmethod_id", webmethodId));
		}
		public DataTable webmethods_select()
		{
			return fillDataTable("SELECT * FROM webmethods");
		}
		#endregion

		#region methods for domain_services
		public int domain_services_update(int domainId, int serviceId)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM domain_services " +
					"WHERE domain_id = @domain_id AND service_id = @service_id ) " +
				"BEGIN " +
					"INSERT INTO domain_services (domain_id, service_id) VALUES (@domain_id, @service_id) " +
				"END",
				new SqlParameter("@domain_id", domainId),
				new SqlParameter("@service_id", serviceId));
		}
		public int domain_services_delete(int domainId, int serviceId)
		{
			return execNonQuery(
				"DELETE FROM domain_services WHERE domain_id = @domain_id AND service_id = @service_id",
			   new SqlParameter("@domain_id", domainId),
			   new SqlParameter("@service_id", serviceId));
		}
		public DataTable domain_services_select(int domainId, int serviceId)
		{
			return fillDataTable(
				"SELECT * FROM domain_services WHERE domain_id = @domain_id AND service_id = @service_id",
			   new SqlParameter("@domain_id", domainId),
			   new SqlParameter("@service_id", serviceId));
		}
		public DataTable domain_services_select()
		{
			return fillDataTable("SELECT * FROM domain_services");
		}
		#endregion

		#region methods for system_services
		public int system_services_update(int systemId, int serviceId)
		{
			return execNonQuery(
				"INSERT INTO system_services (system_id, service_id) VALUES (@system_id, @service_id)",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public int system_services_delete(int systemId, int serviceId)
		{
			return execNonQuery(
				"DELETE FROM system_services WHERE system_id = @system_id AND service_id = @service_id",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable system_services_select(int systemId, int serviceId)
		{
			return fillDataTable(
				"SELECT * FROM system_services WHERE system_id = @system_id AND service_id = @service_id",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable system_services_select()
		{
			return fillDataTable("SELECT * FROM system_services");
		}
		#endregion

		#region methods for schema_variables
		public int schema_variables_update(int systemId, int serviceId)
		{
			return execNonQuery(
				"INSERT INTO system_services (system_id, service_id) VALUES (@system_id, @service_id)",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public int schema_variables_delete(int systemId, int serviceId)
		{
			return execNonQuery(
				"DELETE FROM system_services WHERE system_id = @system_id AND service_id = @service_id",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable schema_variables_select(int systemId, int serviceId)
		{
			return fillDataTable(
				"SELECT * FROM system_services WHERE system_id = @system_id AND service_id = @service_id",
				new SqlParameter("@system_id", systemId),
				new SqlParameter("@service_id", serviceId));
		}
		public DataTable schema_variables_select()
		{
			return fillDataTable("SELECT * FROM system_services");
		}

		#endregion

		#region methods for business rules

		public void domain_business_rules_update(int domainID, string ruleName)
		{
			SqlParameter[] sqlparams = {
				(domainID >= 0 ?
				 new SqlParameter("@domain_id", domainID) :
				 new SqlParameter("@domain_id", DBNull.Value) ),
				new SqlParameter("@name", ruleName),
				new SqlParameter("@org_id", this.ProcessData.UserData.OrganizationID)
			};
			execNonQuery(
				"IF NOT EXISTS (SELECT * FROM domain_business_rules " +
					"WHERE domain_id = @domain_id AND name = @name AND org_id = @org_id) " +
				"BEGIN " +
					"INSERT INTO domain_business_rules (domain_id, name, org_id) " +
					"VALUES (@domain_id, @name, @org_id) " +
				"END",
				sqlparams);
		}
		public void domain_business_rules_delete(int domainID, string ruleName)
		{
			execNonQuery(
				"DELETE FROM domain_business_rules " +
				"WHERE domain_id = @domain_id AND name = @name AND org_id = @org_id",
				new SqlParameter("@domain_id", domainID),
				new SqlParameter("@name", ruleName),
				new SqlParameter("@org_id", this.ProcessData.UserData.OrganizationID));
		}
		public DataTable domain_business_rules_select(int domainID)
		{
			return fillDataTable(
				"SELECT br.* FROM Business_Rules br " +
				"INNER JOIN domain_business_rules dbr ON dbr.name = br.name AND dbr.org_id = br.org_id " +
				"WHERE dbr.domain_id = @domain_id AND br.org_id = @org_id",
				new SqlParameter("@domain_id", domainID),
				new SqlParameter("@org_id", this.ProcessData.UserData.OrganizationID));
		}
		public DataTable root_domain_business_rules_select()
		{
			return fillDataTable(
				"SELECT br.* FROM Business_Rules br " +
				"INNER JOIN domain_business_rules dbr ON dbr.name = br.name AND dbr.org_id = br.org_id " +
				"WHERE dbr.domain_id IS NULL AND br.org_id = @org_id",
				new SqlParameter("@org_id", this.ProcessData.UserData.OrganizationID));
		}
		public DataRow business_rules_select(string ruleName)
		{
			DataTable dt = fillDataTable(
				"SELECT * FROM Business_Rules WHERE name = @name AND org_id = @org_id",
				new SqlParameter("@name", ruleName),
				new SqlParameter("@org_id", Global.Data.UserAccess.UserData.OrganizationID));

			if ( dt != null && dt.Rows.Count > 0 )
				return dt.Rows[0];

			return null;
		}

		#endregion

		#region svcmethod_kpi

		public int svcmethod_kpi_update(int svcmethodId, int kpiId, int metricId)
		{
			int iRet = -1;
			if ( (int)execScalar("SELECT count(*) FROM operation_kpi " +
								 "WHERE operation_id = @operation_id AND kpi_id = @kpi_id " +
									"AND metric_id = @metric_id",
								 new SqlParameter("@operation_id", svcmethodId),
								 new SqlParameter("@kpi_id", kpiId),
								 new SqlParameter("@metric_id", metricId)) == 0 ) {
				iRet = execNonQuery(
					"INSERT INTO operation_kpi (operation_id, kpi_id, metric_id) " +
					"VALUES (@operation_id, @kpi_id, @metric_id)",
					new SqlParameter("@operation_id", svcmethodId),
					new SqlParameter("@kpi_id", kpiId),
					new SqlParameter("@metric_id", metricId));
			}
			return iRet;
		}
		public int svcmethod_kpi_delete(int svcmethodId, int kpiId, int metricId)
		{
			return execNonQuery("DELETE FROM operation_kpi " +
								"WHERE operation_id = @operation_id AND kpi_id = @kpi_id " +
									"AND metric_id = @metric_id",
								new SqlParameter("@operation_id", svcmethodId),
								new SqlParameter("@kpi_id", kpiId),
								new SqlParameter("@metric_id", metricId));
		}
		public DataTable svcmethod_kpi_select(int svcmethodId)
		{
			return fillDataTable("SELECT * FROM operation_kpi " +
								"WHERE operation_id = @operation_id",
								new SqlParameter("@operation_id", svcmethodId));
		}

		#endregion svcmethod_kpi

		#region key_performance_indicators

		public int kpi_update(KeyPerformanceIndicator kpi)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@kpi_id", kpi.ID),
                new SqlParameter("@kpi_nm", kpi.Name),
                new SqlParameter("@kpi_desc", kpi.Description),
                new SqlParameter("@kpi_type", kpi.Type),
                new SqlParameter("@kpi_ntext", kpi.NText),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID)
            };

			if ( (int)execScalar("SELECT count(*) FROM key_performance_indicators WHERE kpi_id = @kpi_id",
								 new SqlParameter("@kpi_id", kpi.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO key_performance_indicators (kpi_nm, kpi_desc, kpi_type, kpi_ntext, org_id) " +
					"VALUES (@kpi_nm, @kpi_desc, @kpi_type, @kpi_ntext, @org_id); " +
					"SELECT kpi_id FROM key_performance_indicators WHERE kpi_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE key_performance_indicators SET kpi_nm = @kpi_nm, kpi_desc = @kpi_desc, " +
						"kpi_type = @kpi_type, kpi_ntext = @kpi_ntext, org_id = @org_id " +
					"WHERE kpi_id = @kpi_id; " +
					"SELECT @kpi_id",
					sqlParams);
			}
			return iRet;
		}
		public int kpi_delete(int kpiId)
		{
			execNonQuery("DELETE FROM svcmethod_kpi WHERE kpi_id = @kpi_id",
				new SqlParameter("@kpi_id", kpiId));
			return execNonQuery("DELETE FROM key_performance_indicators WHERE kpi_id = @kpi_id",
				new SqlParameter("@kpi_id", kpiId));
		}
		public DataTable kpi_select(int kpiId)
		{
			return fillDataTable("SELECT * FROM key_performance_indicators WHERE kpi_id = @kpi_id",
				new SqlParameter("@kpi_id", kpiId));
		}
		public DataTable kpi_select()
		{
			return fillDataTable("SELECT * FROM key_performance_indicators WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}

		#endregion key_performance_indicators

		#region kpi_metrics

		public int kpi_metrics_update(KPIMetric metric)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@metric_id", metric.ID),
                new SqlParameter("@metric_nm", metric.Name),
                new SqlParameter("@metric_desc", metric.Description),
				(metric.DataType == null || metric.DataType == "" ? 
					new SqlParameter("@metric_datatype", DBNull.Value) :
					new SqlParameter("@metric_datatype", metric.DataType)),
                new SqlParameter("@metric_ntext", metric.NText),
                new SqlParameter("@metric_value", metric.Value),
                new SqlParameter("@metric_unit", metric.Unit),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID)
			};

			if ( (int)execScalar("SELECT count(*) FROM kpi_metrics WHERE metric_id = @metric_id",
								new SqlParameter("@metric_id", metric.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO kpi_metrics (metric_nm, metric_desc, metric_datatype, metric_ntext, " +
						"metric_value, metric_unit, org_id) " +
					"VALUES (@metric_nm, @metric_desc, @metric_datatype, @metric_ntext, " +
						"@metric_value, @metric_unit, @org_id); " +
					"SELECT metric_id FROM kpi_metrics WHERE metric_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE kpi_metrics SET metric_nm = @metric_nm, metric_desc = @metric_desc, " +
						"metric_datatype = @metric_datatype, metric_ntext = @metric_ntext, " +
						"metric_value = @metric_value, metric_unit = @metric_unit, org_id = @org_id " +
					"WHERE metric_id = @metric_id; " +
					"SELECT @metric_id",
					sqlParams);
			}

			return iRet;
		}
		public int kpi_metrics_delete(int metricId)
		{
			execNonQuery("DELETE FROM svcmethod_kpi WHERE metric_id = @metric_id",
				new SqlParameter("@metric_id", metricId));
			execNonQuery("DELETE FROM kpi_schedule ks " +
				"INNER JOIN kpi_functions kf ON kf.function_id = ks.function_id " +
				"WHERE kf.metric_id = @metric_id",
				new SqlParameter("@metric_id", metricId));
			execNonQuery("DELETE FROM kpi_functions WHERE metric_id = @metric_id",
				new SqlParameter("@metric_id", metricId));
			return execNonQuery("DELETE FROM kpi_metrics WHERE metric_id = @metric_id",
				new SqlParameter("@metric_id", metricId));
		}
		public DataTable kpi_metrics_select(int metricId)
		{
			return fillDataTable("SELECT * FROM kpi_metrics WHERE metric_id = @metric_id",
				new SqlParameter("@metric_id", metricId));
		}
		public DataTable kpi_metrics_select()
		{
			return fillDataTable("SELECT * FROM kpi_metrics WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}

		#endregion kpi_metrics

		#region kpi_functions

		public int kpi_functions_update(KPIFunction function)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@function_id", function.ID),
                new SqlParameter("@function_nm", function.Name),
                new SqlParameter("@function_desc", function.Description),
                new SqlParameter("@function_ntext", function.NText),
                new SqlParameter("@function_code", function.Code),
				new SqlParameter("@metric_id", function.MetricID)
            };

			if ( (int)execScalar("SELECT count(*) FROM kpi_functions WHERE function_id = @function_id",
								new SqlParameter("@function_id", function.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO kpi_functions (function_nm, function_desc, function_ntext, function_code, " +
						"metric_id) " +
					"VALUES (@function_nm, @function_desc, @function_ntext, @function_code, " +
						"@metric_id); " +
					"SELECT function_id FROM kpi_functions WHERE function_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE kpi_functions SET function_nm = @function_nm, function_desc = @function_desc, " +
						"function_ntext = @function_ntext, function_code = @function_code, " +
						"metric_id = @metric_id " +
					"WHERE function_id = @function_id; " +
					"SELECT @function_id",
					sqlParams);
			}

			return iRet;
		}
		public int kpi_functions_delete(int functionId)
		{
			execNonQuery("DELETE FROM kpi_schedule WHERE function_id = @function_id",
				new SqlParameter("@function_id", functionId));
			return execNonQuery("DELETE FROM kpi_functions WHERE function_id = @function_id",
				new SqlParameter("@function_id", functionId));
		}
		public DataTable kpi_functions_select(int functionId)
		{
			return fillDataTable("SELECT * FROM kpi_functions WHERE function_id = @function_id",
				new SqlParameter("@function_id", functionId));
		}
		public DataTable kpi_functions_select()
		{
			return fillDataTable("SELECT * FROM kpi_functions");
		}

		#endregion kpi_functions

		#region kpi_schedule

		public int kpi_schedule_update(KPISchedule schedule)
		{
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@schedule_id", schedule.ID),
                new SqlParameter("@schedule_nm", schedule.Name),
                new SqlParameter("@schedule_desc", schedule.Description),
                new SqlParameter("@schedule_ntext", schedule.NText),
                new SqlParameter("@schedule_timespan", schedule.TimeSpan.ToString()),
                new SqlParameter("@function_id", schedule.FunctionID)
            };

			if ( (int)execScalar("SELECT count(*) FROM kpi_schedule WHERE schedule_id = @schedule_id",
								new SqlParameter("@schedule_id", schedule.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO kpi_schedule (schedule_nm, schedule_desc, schedule_ntext, " +
						"schedule_timespan, function_id) " +
					"VALUES (@schedule_nm, @schedule_desc, @schedule_ntext, @schedule_timespan, " +
						"@function_id); " +
					"SELECT schedule_id FROM kpi_schedule WHERE schedule_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE kpi_schedule SET schedule_nm = @schedule_nm, schedule_desc = @schedule_desc, " +
						"schedule_ntext = @schedule_ntext, " +
						"schedule_timespan = @schedule_timespan, function_id = @function_id " +
					"WHERE schedule_id = @schedule_id; " +
					"SELECT @schedule_id",
					sqlParams);
			}

			return iRet;
		}
		public int kpi_schedule_delete(int scheduleId)
		{
			return execNonQuery("DELETE FROM kpi_schedule WHERE schedule_id = @schedule_id",
				new SqlParameter("@schedule_id", scheduleId));
		}
		public DataTable kpi_schedule_select(int scheduleId)
		{
			return fillDataTable("SELECT * FROM kpi_schedule WHERE schedule_id = @schedule_id",
				new SqlParameter("@schedule_id", scheduleId));
		}
		public DataTable kpi_schedule_select()
		{
			return fillDataTable("SELECT * FROM kpi_schedule");
		}

		#endregion kpi_schedule

		#region domain_business_objects

		public int domain_business_objects_update(BusinessObject bobject)
		{
			int iRet = -1;
			string schema = null;
			if ( bobject.Schema != null ) {
				XmlWriterSettings settings = new XmlWriterSettings();
				settings.Indent = false;
				settings.OmitXmlDeclaration = true;
				settings.ConformanceLevel = ConformanceLevel.Auto;
				using ( StringWriter sw = new StringWriter() )
				using ( XmlWriter xw = XmlWriter.Create(sw, settings) ) {
					bobject.Schema.Write(xw);
					xw.Flush();
					schema = sw.ToString();
				}
			}
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@bobj_id", bobject.ID),
                new SqlParameter("@bobj_nm", bobject.Name),
                new SqlParameter("@bobj_desc", bobject.Description),
                new SqlParameter("@bobj_ntext", bobject.NText),
                (schema != null ?
					new SqlParameter("@bobj_schema", schema) :
					new SqlParameter("@bobj_schema", DBNull.Value)),
				//(bobject.DomainID > 0 ?
				//    new SqlParameter("@domain_id", bobject.DomainID) :
				//    new SqlParameter("@domain_id", DBNull.Value)),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID)
            };

			if ( (int)execScalar("SELECT count(*) FROM domain_business_objects WHERE bobj_id = @bobj_id",
								new SqlParameter("@bobj_id", bobject.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO domain_business_objects (bobj_nm, bobj_desc, bobj_ntext, bobj_schema, " +
						"domain_id, org_id) " +
					"VALUES (@bobj_nm, @bobj_desc, @bobj_ntext, @bobj_schema, " +
						"@domain_id, @org_id); " +
					"SELECT bobj_id FROM domain_business_objects WHERE bobj_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE domain_business_objects SET bobj_nm = @bobj_nm, bobj_desc = @bobj_desc, " +
						"bobj_ntext = @bobj_ntext, bobj_schema = @bobj_schema, " +
						"domain_id = @domain_id, org_id = @org_id " +
					"WHERE bobj_id = @bobj_id; " +
					"SELECT @bobj_id",
					sqlParams);
			}

			return iRet;
		}
		public int domain_business_objects_delete(int bobjectid)
		{
			execNonQuery("DELETE FROM domain_business_object_vocabulary WHERE bobj_id = @bobj_id",
				new SqlParameter("@bobj_id", bobjectid));
			execNonQuery("DELETE FROM business_object_hierarchy WHERE parent_bobj_id = @parent_bobj_id",
				new SqlParameter("@parent_bobj_id", bobjectid));
			execNonQuery("DELETE FROM business_object_hierarchy WHERE child_bobj_id = @child_bobj_id",
				new SqlParameter("@child_bobj_id", bobjectid));
			return execNonQuery("DELETE FROM domain_business_objects WHERE bobj_id = @bobj_id",
				new SqlParameter("@bobj_id", bobjectid));
		}
		public DataTable domain_business_objects_select(int bobjectid)
		{
			return fillDataTable("SELECT * FROM domain_business_objects WHERE bobj_id = @bobj_id",
				new SqlParameter("@bobj_id", bobjectid));
		}
		public DataTable domain_business_objects_select()
		{
			return fillDataTable("SELECT * FROM domain_business_objects WHERE org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable root_domain_business_objects_select()
		{
			return fillDataTable(
				"SELECT * FROM domain_business_objects " +
				"WHERE org_id = @org_id AND domain_id IS NULL AND " +
				" bobj_id NOT IN (SELECT child_bobj_id FROM business_object_hierarchy)",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable domain_business_objects_select_by_domain(int domainId)
		{
			return fillDataTable(
				"SELECT * FROM domain_business_objects " +
				"WHERE org_id = @org_id AND domain_id = @domain_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID),
				new SqlParameter("@domain_id", domainId));
		}
		public DataTable domain_business_objects_select_by_parent_bobjectId(int parentBObjId)
		{
			return new DataTable();
			//return fillDataTable(
			//    "SELECT bo.* FROM domain_business_objects bo " +
			//    "INNER JOIN business_object_hierarchy boh ON boh.child_bobj_id = bo.bobj_id " + 
			//    "WHERE boh.parent_bobj_id = @parent_bobj_id",
			//    new SqlParameter("@parent_bobj_id", parentBObjId));
		}

		public BusinessObject LoadBObject(int bobjectId)
		{
			BusinessObject bobj = null;
			DataTable dtBObj = domain_business_objects_select(bobjectId);
			if ( dtBObj != null && dtBObj.Rows.Count > 0 ) {
				bobj = new BusinessObject();
				//bobj.Load(dtBObj.Rows[0]);
			}
			return bobj;
		}
		public DataRow SaveBObject(BusinessObject bobject)
		{
			DataRow row = null;
			//int id = domain_business_objects_update(bobject);
			//DataTable dt = domain_business_objects_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion domain_business_objects

		#region domain_business_object_vocabulary
		public int domain_business_object_vocabulary_update(int bobjectId, int domvocabId)
		{
			return execNonQuery(
				"INSERT INTO domain_business_object_vocabulary (bobj_id, domvocab_id) " +
				"VALUES (@bobj_id, @domvocab_id)",
				new SqlParameter("@bobj_id", bobjectId),
				new SqlParameter("@domvocab_id", domvocabId));
		}
		public int domain_business_object_vocabulary_delete(int bobjectId, int domvocabId)
		{
			return execNonQuery(
				"DELETE FROM domain_business_object_vocabulary " +
				"WHERE bobj_id = @bobj_id AND domvocab_id = @domvocab_id",
			   new SqlParameter("@bobj_id", bobjectId),
			   new SqlParameter("@domvocab_id", domvocabId));
		}
		public DataTable domain_business_object_vocabulary_select_by_bobject(int bobjectId)
		{
			return fillDataTable(
				"SELECT * FROM domain_business_object_vocabulary WHERE bobj_id = @bobj_id",
			   new SqlParameter("@bobj_id", bobjectId));
		}
		public DataTable domain_business_object_vocabulary_select()
		{
			return fillDataTable("SELECT * FROM domain_business_object_vocabulary");
		}
		#endregion

		#region business_object_hierarchy
		public int business_object_hierarchy_update(int parentBObjId, int childBObjId)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM business_object_hierarchy " +
					"WHERE parent_bobj_id = @parent_bobj_id AND child_bobj_id = @child_bobj_id ) " +
				"BEGIN " +
					"INSERT INTO business_object_hierarchy (parent_bobj_id, child_bobj_id) " +
					"VALUES (@parent_bobj_id, @child_bobj_id) " +
				"END",
				new SqlParameter("@parent_bobj_id", parentBObjId),
				new SqlParameter("@child_bobj_id", childBObjId));
		}
		public int business_object_hierarchy_delete(int parentBObjId, int childBObjId)
		{
			return execNonQuery(
				"DELETE FROM business_object_hierarchy " +
				"WHERE parent_bobj_id = @parent_bobj_id AND child_bobj_id = @child_bobj_id",
			   new SqlParameter("@parent_bobj_id", parentBObjId),
			   new SqlParameter("@child_bobj_id", childBObjId));
		}
		public DataTable business_object_hierarchy_select_by_parent_bobject(int parentBObjId)
		{
			return fillDataTable(
				"SELECT * FROM business_object_hierarchy WHERE parent_bobj_id = @parent_bobj_id",
			   new SqlParameter("@parent_bobj_id", parentBObjId));
		}
		public DataTable business_object_hierarchy_select()
		{
			return fillDataTable("SELECT * FROM business_object_hierarchy");
		}
		#endregion business_object_hierarchy

		#region operation_bpel_process
		public int operation_bpel_process_update(int operationId, Guid bpelGuid)
		{
			return execNonQuery(
				"UPDATE operations SET operation_bpel_guid = @bpel_guid " +
				"WHERE @operation_id = @operation_id ",
				new SqlParameter("@operation_id", operationId),
				new SqlParameter("@bpel_guid", bpelGuid));
		}
		public int operation_bpel_process_delete(int operationId, Guid bpelGuid)
		{
			return execNonQuery(
				"UPDATE operations SET operation_bpel_guid = NULL " +
				"WHERE @operation_id = @operation_id AND operation_bpel_guid = @bpel_guid",
			   new SqlParameter("@operation_id", operationId),
			   new SqlParameter("@bpel_guid", bpelGuid));
		}
		public Guid operation_bpel_process_select(int operationId)
		{
			Guid bpelGuid = Guid.Empty;

			object obj = execScalar(
				"SELECT o.operation_bpel_guid FROM operations o " +
				"INNER JOIN bpel_process b ON b.bpel_guid = o.bpel_guid " +
				"WHERE o.operation_id = @operation_id AND b.deleted_ind != 1",
				new SqlParameter("@operation_id", operationId));

			if ( obj is Guid )
				bpelGuid = (Guid)obj;

			return bpelGuid;
		}
		#endregion operation_bpel_process

		#region operation_log_ep
		public int operation_log_ep_update(int operationId, Guid logEPGuid)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM operation_log_ep " +
					"WHERE operation_id = @operation_id AND log_ep_guid = @log_ep_guid ) " +
				"BEGIN " +
					"INSERT INTO operation_log_ep (operation_id, log_ep_guid) " +
					"VALUES (@operation_id, @log_ep_guid) " +
				"END",
				new SqlParameter("@operation_id", operationId),
				new SqlParameter("@log_ep_guid", logEPGuid));
		}
		public int operation_log_ep_update(int operationId, Guid logEPGuid, string xsltDocument)
		{
			return execNonQuery(
				"IF NOT EXISTS ( SELECT * FROM operation_log_ep " +
					"WHERE operation_id = @operation_id AND log_ep_guid = @log_ep_guid ) " +
				"BEGIN " +
					"INSERT INTO operation_log_ep (operation_id, log_ep_guid, input_xslt) " +
					"VALUES (@operation_id, @log_ep_guid, @input_xslt) " +
				"ELSE " +
					"UPDATE operation_log_ep SET input_xslt = @input_xslt " +
					"WHERE operation_id = @operation_id AND log_ep_guid = @log_ep_guid " +
				"END",
				new SqlParameter("@operation_id", operationId),
				new SqlParameter("@log_ep_guid", logEPGuid),
				new SqlParameter("@input_xslt", xsltDocument));
		}
		public int operation_log_ep_update(Guid logEPGuid, string xsltDocument, string mappingxml)
		{
			return execNonQuery(
				"UPDATE operation_log_ep SET input_xslt = @input_xslt, input_mapping = @input_mapping " +
				"WHERE log_ep_guid = @log_ep_guid ",
				new SqlParameter("@log_ep_guid", logEPGuid),
				new SqlParameter("@input_mapping", mappingxml),
				new SqlParameter("@input_xslt", xsltDocument));
		}
		public int operation_log_ep_delete(int operationId, Guid logEPGuid)
		{
			return execNonQuery(
				"DELETE FROM operation_log_ep " +
				"WHERE operation_id = @operation_id AND log_ep_guid = @log_ep_guid",
			   new SqlParameter("@operation_id", operationId),
			   new SqlParameter("@log_ep_guid", logEPGuid));
		}
		public int operation_log_ep_delete(Guid logEPGuid)
		{
			return execNonQuery(
				"DELETE FROM operation_log_ep " +
				"WHERE log_ep_guid = @log_ep_guid",
			   new SqlParameter("@log_ep_guid", logEPGuid));
		}

		public DataTable operation_log_ep_select_by_operation_id(int operationId)
		{
			return fillDataTable(
				"SELECT * FROM operation_log_ep WHERE operation_id = @operation_id",
				new SqlParameter("@operation_id", operationId));
		}
		public DataTable operation_log_ep_select_by_log_ep_guid(Guid logEPGuid)
		{
			return fillDataTable(
				"SELECT * FROM operation_log_ep WHERE log_ep_guid = @log_ep_guid",
				new SqlParameter("@log_ep_guid", logEPGuid));
		}
		public Guid operation_log_ep_select_log_ep_guid(int operationId)
		{
			Guid logEPGuid = Guid.Empty;

			object obj = execScalar(
				"SELECT log_ep_guid FROM operation_log_ep WHERE operation_id = @operation_id",
				new SqlParameter("@operation_id", operationId));

			if ( obj is Guid )
				logEPGuid = (Guid)obj;

			return logEPGuid;
		}
		public int operation_log_ep_select_operation_id(Guid logEPGuid)
		{
			int operationId = -1;

			object obj = execScalar(
				"SELECT operation_id FROM operation_log_ep WHERE log_ep_guid = @log_ep_guid",
				new SqlParameter("@log_ep_guid", logEPGuid));

			if ( obj is int )
				operationId = (int)obj;

			return operationId;
		}

		public Operation LoadOperationByLogEP(Guid logEPGuid)
		{
			RepositoryService repSvc = (RepositoryService)this.GetService(typeof(RepositoryService));
			if ( repSvc == null )
				throw new NullReferenceException("RepositoryService not available.");

			int operationId = operation_log_ep_select_operation_id(logEPGuid);
			return repSvc.GetItem(ItemType.Operation, operationId, true) as Operation;
		}

		#endregion operation_log_ep

		#region domain_verbs

		public int domain_verbs_update(Verb verb)
		{
			int idOrg = _processData.UserData.OrganizationID;
			int iRet = -1;
			SqlParameter[] sqlParams = 
            {
                new SqlParameter("@verb_id", verb.ID),
                new SqlParameter("@verb_nm", verb.Name),
                new SqlParameter("@verb_desc", verb.Description),
                new SqlParameter("@verb_ntext", verb.NText),
				//(verb.DomainID > 0 ?
				//    new SqlParameter("@domain_id", verb.DomainID) :
				//    new SqlParameter("@domain_id", DBNull.Value)),
                new SqlParameter("@org_id", idOrg)
            };

			if ( (int)execScalar("SELECT count(*) FROM domain_verbs WHERE verb_id = @verb_id",
								new SqlParameter("@verb_id", verb.ID)) == 0 ) {
				iRet = (int)execScalar(
					"INSERT INTO domain_verbs (verb_nm, verb_desc, verb_ntext, domain_id, org_id) " +
					"VALUES (@verb_nm, @verb_desc, @verb_ntext, @domain_id, @org_id); " +
					"SELECT verb_id FROM domain_verbs WHERE verb_id = SCOPE_IDENTITY()",
					sqlParams);
			} else {
				iRet = (int)execScalar(
					"UPDATE domain_verbs SET verb_nm = @verb_nm, verb_desc = @verb_desc, " +
						"verb_ntext = @verb_ntext, domain_id = @domain_id, org_id = @org_id " +
					"WHERE verb_id = @verb_id; " +
					"SELECT @verb_id",
					sqlParams);
			}

			return iRet;
		}
		public int domain_verbs_delete(int verbId)
		{
			return execNonQuery("DELETE FROM domain_verbs WHERE verb_id = @verb_id",
				new SqlParameter("@verb_id", verbId));
		}
		public DataTable domain_verbs_select(int verbId)
		{
			return fillDataTable(
				"SELECT * FROM domain_verbs WHERE verb_id = @verb_id AND org_id = @org_id",
				new SqlParameter("@verb_id", verbId),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable domain_verbs_select_by_domain(int domainId)
		{
			return fillDataTable(
				"SELECT * FROM domain_verbs WHERE domain_id = @domain_id AND org_id = @org_id",
				new SqlParameter("@domain_id", domainId),
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable root_domain_verbs_select()
		{
			return fillDataTable(
				"SELECT * FROM domain_verbs WHERE domain_id IS NULL AND org_id = @org_id",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}

		public Verb LoadDomainVerb(int verbId)
		{
			Verb verb = null;
			DataTable dtVerb = domain_verbs_select(verbId);
			if ( dtVerb != null && dtVerb.Rows.Count > 0 ) {
				verb = new Verb();
				//verb.Load(dtVerb.Rows[0]);
			}
			return verb;
		}
		public DataRow SaveDomainVerb(Verb verb)
		{
			DataRow row = null;
			//int id = domain_verbs_update(verb);
			//DataTable dt = domain_verbs_select(id);
			//if ( dt != null && dt.Rows.Count > 0 )
			//    row = dt.Rows[0];
			return row;
		}

		#endregion domain_verbs

		#region domaintree_folders
		public DataTable domaintree_folders_generic_select()
		{
			return fillDataTable(
				"SELECT * FROM domaintree_folders " +
				"WHERE dtfolder_isgeneric = 1 AND (org_id IS NULL OR org_id = @org_id)",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}
		public DataTable domaintree_folders_domain_select()
		{
			return fillDataTable(
				"SELECT * FROM domaintree_folders " +
				"WHERE dtfolder_isgeneric = 0 AND (org_id IS NULL OR org_id = @org_id)",
				new SqlParameter("@org_id", _processData.UserData.OrganizationID));
		}

		#endregion domaintree_folders
		#endregion

		#region IServiceProvider Members

		public object GetService(Type serviceType)
		{
			if ( _serviceProvider != null )
				return _serviceProvider.GetService(serviceType);
			else
				return null;
		}

		#endregion
	}
}
